package tbdp.tool.core.db;


import org.apache.commons.lang.StringUtils;

import java.sql.*;
import java.util.*;
import java.util.concurrent.Callable;

/**
 * @author xbkaishui
 * db 工具类
 * @version $Id: DBUtil, v 0.1 2016 06 07 下午11:27 xbkaishui Exp $$
 */
public class DBUtil {

	static final int TIMEOUT_SECONDS = 3;

	static final int MAX_TRY_TIMES = 4;


	private DBUtil() {
	}

	/**
	 * 检查表是否具有insert 权限
	 * insert on *.* 或者 insert on database.* 时验证通过
	 * 当insert on database.tableName时，确保tableList中的所有table有insert 权限，验证通过
	 * 其它验证都不通过
	 *
	 * @author ZiChi
	 * @version 1.0 2015-01-28
	 */
	public static boolean hasInsertPrivilege(DataBaseType dataBaseType, String jdbcURL,
			String userName, String password,
			List<String> tableList) {
				/*准备参数*/
		String[] urls = jdbcURL.split("/");
		String dbName;
		if (urls != null && urls.length != 0) {
			dbName = urls[3];
		} else
			return false;

		String dbPattern = "`" + dbName + "`.*";
		Collection<String> tableNames = new HashSet<String>(tableList.size());
		tableNames.addAll(tableList);

		Connection connection = connect(dataBaseType, jdbcURL, userName, password);
		try {
			ResultSet rs = query(connection, "SHOW GRANTS FOR " + userName);
			while (rs.next()) {
				String grantRecord = rs.getString("Grants for " + userName + "@%");
				String[] params = grantRecord.split("\\`");
				if (params != null && params.length >= 3) {
					String tableName = params[3];
					if (!tableName.equals("*") && tableNames.contains(tableName))
						tableNames.remove(tableName);
				} else {
					if (grantRecord.contains("INSERT")) {
						if (grantRecord.contains("*.*"))
							return true;
						else if (grantRecord.contains(dbPattern)) {
							return true;
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (tableNames.isEmpty())
			return true;
		return false;
	}

	/**
	 * Get direct JDBC connection
	 * <p/>
	 * if connecting failed, try to connect for MAX_TRY_TIMES times
	 * <p/>
	 * NOTE: In DataX, we don't need connection pool in fact
	 */
	public static Connection getConnection(final DataBaseType dataBaseType, final String jdbcUrl,
			final String username, final String password) {

		try {
			return RetryUtil.executeWithRetry(new Callable<Connection>() {
				public Connection call() throws Exception {
					return DBUtil.connect(dataBaseType, jdbcUrl, username, password);
				}
			}, MAX_TRY_TIMES, 1000L, true);
		} catch (Exception e) {
			throw DBException.asDBException(DBErrorCode.CONN_DB_ERROR,
					String.format("数据库连接失败. 因为根据您配置的连接信息:%s获取数据库连接失败. 请检查您的配置并作出修改.", jdbcUrl), e);
		}

	}

	public static void getPrimaryKeysInfo(Connection conn, String schema, String table) {
		ResultSet rs = null;
		try {
			DatabaseMetaData dbmd = conn.getMetaData();
			rs = dbmd.getPrimaryKeys(null, schema, table);

			while (rs.next()) {
				String tableCat = rs.getString("TABLE_CAT"); //表类别(可为null)
				String tableSchemaName = rs.getString("TABLE_SCHEM");//表模式（可能为空）,在oracle中获取的是命名空间,其它数据库未知
				String tableName = rs.getString("TABLE_NAME"); //表名
				String columnName = rs.getString("COLUMN_NAME");//列名
				short keySeq = rs.getShort("KEY_SEQ");//序列号(主键内值1表示第一列的主键，值2代表主键内的第二列)
				String pkName = rs.getString("PK_NAME"); //主键名称
				System.out.println(tableCat + " - " + tableSchemaName + " - " + tableName + " - "
						+ columnName + " - " + keySeq + " - " + pkName);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * @throws
	 * @Description: 获取表索引信息
	 * @author: chenzw
	 * @CreateTime: 2014-1-27 下午5:12:04
	 */
	public static Map<String, List<String>> getIndexInfo(Connection conn, String schema,
			String table, boolean unique) {
		Map<String, List<String>> indexs = new HashMap<String, List<String>>();
		ResultSet rs = null;
		try {
			DatabaseMetaData dbmd = conn.getMetaData();
			/**
			 * 获取给定表的索引和统计信息的描述
			 * 方法原型:ResultSet getIndexInfo(String catalog,String schema,String table,boolean unique,boolean approximate)
			 * catalog - 表所在的类别名称;""表示获取没有类别的列,null表示获取所有类别的列。
			 * schema - 表所在的模式名称(oracle中对应于Tablespace);""表示获取没有模式的列,null标识获取所有模式的列; 可包含单字符通配符("_"),或多字符通配符("%");
			 * table - 表名称;可包含单字符通配符("_"),或多字符通配符("%");
			 * unique - 该参数为 true时,仅返回唯一值的索引; 该参数为 false时,返回所有索引;
			 * approximate - 该参数为true时,允许结果是接近的数据值或这些数据值以外的值;该参数为 false时,要求结果是精确结果;
			 */
			rs = dbmd.getIndexInfo(null, schema, table, unique, false);
			while (rs.next()) {
				String tableCat = rs.getString("TABLE_CAT"); //表类别(可为null)
				String tableSchemaName = rs.getString("TABLE_SCHEM");//表模式（可能为空）,在oracle中获取的是命名空间,其它数据库未知
				String tableName = rs.getString("TABLE_NAME"); //表名
				boolean nonUnique =
						rs.getBoolean("NON_UNIQUE");// 索引值是否可以不唯一,TYPE为 tableIndexStatistic时索引值为 false;
				String indexQualifier =
						rs.getString("INDEX_QUALIFIER");//索引类别（可能为空）,TYPE为 tableIndexStatistic 时索引类别为 null;
				String indexName =
						rs.getString("INDEX_NAME");//索引的名称 ;TYPE为 tableIndexStatistic 时索引名称为 null;
				/**
				 * 索引类型：
				 *  tableIndexStatistic - 此标识与表的索引描述一起返回的表统计信息
				 *  tableIndexClustered - 此为集群索引
				 *  tableIndexHashed - 此为散列索引
				 *  tableIndexOther - 此为某种其他样式的索引
				 */
				short type = rs.getShort("TYPE");//索引类型; oracle 0 ,1  mysql 1,3
				String columnName = rs.getString("COLUMN_NAME");//列名;TYPE为 tableIndexStatistic时列名称为 null;
				//                String ascOrDesc = rs.getString("ASC_OR_DESC");//列排序顺序:升序还是降序[A:升序; B:降序];如果排序序列不受支持,可能为 null;TYPE为 tableIndexStatistic时排序序列为 null;
				//                int cardinality = rs.getInt("CARDINALITY"); //基数;TYPE为 tableIndexStatistic 时,它是表中的行数;否则,它是索引中唯一值的数量。
				//                int pages = rs.getInt("PAGES"); //TYPE为 tableIndexStatisic时,它是用于表的页数,否则它是用于当前索引的页数。
				//                String filterCondition = rs.getString("FILTER_CONDITION"); //过滤器条件,如果有的话(可能为 null)。
				if (indexName != null) {
					List<String> columns = indexs.get(indexName);
					if (columns == null) {
						columns = new LinkedList<String>();
						columns.add(columnName);
						indexs.put(indexName, columns);
					} else {
						columns.add(columnName);
					}
				}
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		return indexs;
	}

	public static void closeConnection(Connection conn) {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (Exception e) {
			//ignore
		}
	}

	private static synchronized Connection connect(DataBaseType dataBaseType, String url,
			String user, String pass) {
		try {
			Class.forName(dataBaseType.getDriverClassName());
			DriverManager.setLoginTimeout(TIMEOUT_SECONDS);
			return DriverManager.getConnection(url, user, pass);
		} catch (Exception e) {
			throw DBException.asDBException(DBErrorCode.CONN_DB_ERROR, "连接数据库失败.", e);
		}
	}

	/**
	 * a wrapped method to execute select-like sql statement .
	 *
	 * @param conn Database connection .
	 * @param sql  sql statement to be executed
	 * @return a {@link ResultSet}
	 * @throws SQLException if occurs SQLException.
	 */
	public static ResultSet query(Connection conn, String sql, int fetchSize) throws SQLException {
		// make sure autocommit is off
		conn.setAutoCommit(false);
		Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
				ResultSet.CONCUR_READ_ONLY);
		stmt.setFetchSize(fetchSize);
		return query(stmt, sql);
	}

	/**
	 * a wrapped method to execute select-like sql statement .
	 *
	 * @param stmt {@link Statement}
	 * @param sql  sql statement to be executed
	 * @return a {@link ResultSet}
	 * @throws SQLException if occurs SQLException.
	 */
	public static ResultSet query(Statement stmt, String sql) throws SQLException {
		return stmt.executeQuery(sql);
	}

	public static void executeSqlWithoutResultSet(Statement stmt, String sql) throws SQLException {
		stmt.execute(sql);
	}

	/**
	 * Close {@link ResultSet}, {@link Statement} referenced by this
	 * {@link ResultSet}
	 *
	 * @param rs {@link ResultSet} to be closed
	 * @throws IllegalArgumentException
	 */
	public static void closeResultSet(ResultSet rs) {
		try {
			if (null != rs) {
				Statement stmt = rs.getStatement();
				if (null != stmt) {
					stmt.close();
					stmt = null;
				}
				rs.close();
			}
			rs = null;
		} catch (SQLException e) {
			throw new IllegalStateException(e);
		}
	}

	public static void closeDBResources(ResultSet rs, Statement stmt, Connection conn) {
		if (null != rs) {
			try {
				rs.close();
			} catch (SQLException unused) {
			}
		}

		if (null != stmt) {
			try {
				stmt.close();
			} catch (SQLException unused) {
			}
		}

		if (null != conn) {
			try {
				conn.close();
			} catch (SQLException unused) {
			}
		}
	}

	public static void closeDBResources(Statement stmt, Connection conn) {
		closeDBResources(null, stmt, conn);
	}

	public static List<String> getTableColumns(DataBaseType dataBaseType, String jdbcUrl,
			String user, String pass, String tableName) {
		Connection conn = getConnection(dataBaseType, jdbcUrl, user, pass);
		return getTableColumnsByConn(conn, tableName, "jdbcUrl:" + jdbcUrl);
	}

	public static Map<String, String> getTableMetaColumns(DataBaseType dataBaseType,
			String jdbcUrl, String user, String pass,
			String tableName, String columns) {
		Connection conn = getConnection(dataBaseType, jdbcUrl, user, pass);
		return getTableMetaColumnsByConn(conn, tableName, columns, "jdbcUrl:" + jdbcUrl);
	}

	public static List<String> getTableColumnsByConn(Connection conn, String tableName,
			String basicMsg) {
		List<String> columns = new ArrayList<String>();
		Statement statement = null;
		ResultSet rs = null;
		try {
			statement = conn.createStatement();
			String queryColumnSql = String.format("select * from %s where 1=2", tableName);
			rs = statement.executeQuery(queryColumnSql);
			ResultSetMetaData rsMetaData = rs.getMetaData();
			for (int i = 0, len = rsMetaData.getColumnCount(); i < len; i++) {
				columns.add(rsMetaData.getColumnName(i + 1));
			}

		} catch (SQLException e) {
			throw DBException
					.asDBException(
							DBErrorCode.GET_COLUMN_INFO_FAILED,
							String
									.format(
											"获取字段信息失败. 根据您的配置信息，获取表的所有字段名称时失败. 该错误可能是由于配置错误导致，请检查您的配置信息. 错误配置信息上下文: %s,table:[%s]",
											basicMsg, tableName), e);
		} finally {
			DBUtil.closeDBResources(rs, statement, conn);
		}

		return columns;
	}

	public static Map<String, String> getTableMetaColumnsByConn(Connection conn, String tableName,
			String columns, String basicMsg) {
		Map<String, String> columnsMeta = new LinkedHashMap<String, String>();
		if (StringUtils.isEmpty(columns)) {
			columns = "*";
		}
		Statement statement = null;
		ResultSet rs = null;
		try {
			statement = conn.createStatement();
			String queryColumnSql = String
					.format("select %s from %s where 1=2", columns, tableName);
			rs = statement.executeQuery(queryColumnSql);
			ResultSetMetaData rsMetaData = rs.getMetaData();
			for (int i = 0, len = rsMetaData.getColumnCount(); i < len; i++) {
				columnsMeta.put(rsMetaData.getColumnName(i + 1),
						String.valueOf(rsMetaData.getColumnType(i + 1)));
			}

		} catch (SQLException e) {
			throw DBException
					.asDBException(
							DBErrorCode.GET_COLUMN_INFO_FAILED,
							String
									.format(
											"获取字段信息失败. 根据您的配置信息，获取表的所有字段名称时失败. 该错误可能是由于配置错误导致，请检查您的配置信息. 错误配置信息上下文: %s,table:[%s]",
											basicMsg, tableName), e);
		} finally {
			DBUtil.closeDBResources(rs, statement, conn);
		}

		return columnsMeta;
	}

	public static boolean testConnWithoutRetry(DataBaseType dataBaseType, String url, String user,
			String pass, List<String> preSql) {
		Connection connection = null;
		try {
			connection = connect(dataBaseType, url, user, pass);
			if (null != connection) {
				for (String pre : preSql) {
					if (doPreCheck(connection, pre) == false) {
						return false;
					}
				}
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeDBResources(null, connection);
		}

		return false;
	}

	public static ResultSet query(Connection conn, String sql) throws SQLException {
		Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
				ResultSet.CONCUR_READ_ONLY);
		return query(stmt, sql);
	}

	private static boolean doPreCheck(Connection conn, String pre) {
		ResultSet rs = null;
		try {
			rs = query(conn, pre);

			int checkResult = -1;
			if (rs.next()) {
				checkResult = rs.getInt(1);
				if (rs.next()) {
					return false;
				}

			}

			if (0 == checkResult) {
				return true;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
		}
		return false;
	}

	public static List<Map<String, Object>> iteratorResultSet(ResultSet rs) throws Exception {

		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		ResultSetMetaData md = rs.getMetaData();

		int columnCount = md.getColumnCount(); //Map rowData;

		while (rs.next()) {
			Map<String, Object> rowData = new HashMap<String, Object>();

			for (int i = 1; i <= columnCount; i++) {

				rowData.put(md.getColumnName(i), rs.getObject(i));
			}
			list.add(rowData);
		}
		return list;

	}

}
